﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DBUtility;//请先添加引用
namespace TierApplication
{

    public partial class T_Product
    {

        private decimal _EndQuantity;

        public decimal EndQuantity
        {
            get { return _EndQuantity; }
            set { _EndQuantity = value; }
        }
        private decimal _EndMoney;

        public decimal EndMoney
        {
            get { return _EndMoney; }
            set { _EndMoney = value; }
        }
        private decimal _avgprice;

        public decimal Avgprice
        {
            get { return _avgprice; }
            set { _avgprice = value; }
        }

        private string _UnitName;

        public string UnitName
        {
            get { return _UnitName; }
            set { _UnitName = value; }
        }
        private string _SupplierName;

        public string SupplierName
        {
            get { return _SupplierName; }
            set { _SupplierName = value; }
        }

        private string _ProductTypeName;

        public string ProductTypeName
        {
            get { return _ProductTypeName; }
            set { _ProductTypeName = value; }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public void GetModel(string sProductID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" ProductID,ProductNo,ProductName,ProductNameEn,ProductType,SupplierID,Spec1,Spec2,Spec3,Unit,UnitPrice,UnitPriceOut,DepotUp,DepotDown,Photo,Color,Size,Remark,Point,ProductProp,CreateDate,CreateUser, ");
            strSql.Append("  dbo.f_getProductEndQuantity(a.ProductID) EndQuantity,");
            strSql.Append(" dbo.f_getProductEndMoney(a.ProductID) EndMoney,");
            strSql.Append(" dbo.f_getProductAvgprice(a.ProductID) Avgprice,");
            strSql.Append(" dbo.f_getProductAvgprice(a.ProductID) Avgprice,");
            strSql.Append(" (Select SupplierName from T_Supplier where SupplierID=a.SupplierID) SupplierName, ");
            strSql.Append(" (Select CodeName from C_DMB where CodeID=a.ProductType) ProductTypeName, ");
            strSql.Append(" (Select CodeName from C_Unit where CodeID=a.Unit) UnitName,UsefulLifeType,BaseProductPlace ");
            strSql.Append(" from T_Product a ");
            strSql.Append(" where ProductID='" + sProductID + "' ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                ProductID = ds.Tables[0].Rows[0]["ProductID"].ToString();
                ProductNo = ds.Tables[0].Rows[0]["ProductNo"].ToString();
                ProductName = ds.Tables[0].Rows[0]["ProductName"].ToString();
                ProductNameEn = ds.Tables[0].Rows[0]["ProductNameEn"].ToString();
                ProductType = ds.Tables[0].Rows[0]["ProductType"].ToString();
                SupplierID = ds.Tables[0].Rows[0]["SupplierID"].ToString();
                Spec1 = ds.Tables[0].Rows[0]["Spec1"].ToString();
                Spec2 = ds.Tables[0].Rows[0]["Spec2"].ToString();
                Spec3 = ds.Tables[0].Rows[0]["Spec3"].ToString();
                Unit = ds.Tables[0].Rows[0]["Unit"].ToString();
                if (ds.Tables[0].Rows[0]["UnitPrice"].ToString() != "")
                {
                    UnitPrice = decimal.Parse(ds.Tables[0].Rows[0]["UnitPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UnitPriceOut"].ToString() != "")
                {
                    UnitPriceOut = decimal.Parse(ds.Tables[0].Rows[0]["UnitPriceOut"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DepotUp"].ToString() != "")
                {
                    DepotUp = decimal.Parse(ds.Tables[0].Rows[0]["DepotUp"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DepotDown"].ToString() != "")
                {
                    DepotDown = decimal.Parse(ds.Tables[0].Rows[0]["DepotDown"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Photo"].ToString() != "")
                {
                    //Photo = (byte[])ds.Tables[0].Rows[0]["Photo"];
                }
                Color = ds.Tables[0].Rows[0]["Color"].ToString();
                Size = ds.Tables[0].Rows[0]["Size"].ToString();
                Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                if (ds.Tables[0].Rows[0]["Point"].ToString() != "")
                {
                    Point = decimal.Parse(ds.Tables[0].Rows[0]["Point"].ToString());
                }
                ProductProp = ds.Tables[0].Rows[0]["ProductProp"].ToString();
                if (ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                {
                    CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                }
                CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString();

                if (ds.Tables[0].Rows[0]["EndQuantity"].ToString() != "")
                {
                    EndQuantity = decimal.Parse(ds.Tables[0].Rows[0]["EndQuantity"].ToString());
                }

                if (ds.Tables[0].Rows[0]["EndMoney"].ToString() != "")
                {
                    EndMoney = decimal.Parse(ds.Tables[0].Rows[0]["EndMoney"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Avgprice"].ToString() != "")
                {
                    Avgprice = decimal.Parse(ds.Tables[0].Rows[0]["Avgprice"].ToString());
                }
                UnitName = ds.Tables[0].Rows[0]["UnitName"].ToString();
                SupplierName = ds.Tables[0].Rows[0]["SupplierName"].ToString();
                ProductTypeName = ds.Tables[0].Rows[0]["ProductTypeName"].ToString();
                if (ds.Tables[0].Rows[0]["UsefulLifeType"] != null)
                {
                    this.UsefulLifeType = ds.Tables[0].Rows[0]["UsefulLifeType"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BaseProductPlace"] != null)
                {
                    this.BaseProductPlace = ds.Tables[0].Rows[0]["BaseProductPlace"].ToString();
                }

            }
        }


        public void GetModelExceptPhoto(string sProductID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" ProductID,ProductNo,ProductName,ProductNameEn,ProductType,SupplierID,Spec1,Spec2,Spec3,Unit,UnitPrice,UnitPriceOut,DepotUp,DepotDown,null Photo,Color,Size,Remark,Point,ProductProp,CreateDate,CreateUser, ");
            strSql.Append("  dbo.f_getProductEndQuantity(a.ProductID) EndQuantity,");
            strSql.Append(" dbo.f_getProductEndMoney(a.ProductID) EndMoney,");
            strSql.Append(" dbo.f_getProductAvgprice(a.ProductID) Avgprice,");
            strSql.Append(" dbo.f_getProductAvgprice(a.ProductID) Avgprice,");
            strSql.Append(" (Select SupplierName from T_Supplier where SupplierID=a.SupplierID) SupplierName, ");
            strSql.Append(" (Select CodeName from C_Unit where CodeID=a.Unit) UnitName,UsefulLifeType,BaseProductPlace  ");
            strSql.Append(" from T_Product a ");
            strSql.Append(" where ProductID='" + sProductID + "' ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                ProductID = ds.Tables[0].Rows[0]["ProductID"].ToString();
                ProductNo = ds.Tables[0].Rows[0]["ProductNo"].ToString();
                ProductName = ds.Tables[0].Rows[0]["ProductName"].ToString();
                ProductNameEn = ds.Tables[0].Rows[0]["ProductNameEn"].ToString();
                ProductType = ds.Tables[0].Rows[0]["ProductType"].ToString();
                SupplierID = ds.Tables[0].Rows[0]["SupplierID"].ToString();
                Spec1 = ds.Tables[0].Rows[0]["Spec1"].ToString();
                Spec2 = ds.Tables[0].Rows[0]["Spec2"].ToString();
                Spec3 = ds.Tables[0].Rows[0]["Spec3"].ToString();
                Unit = ds.Tables[0].Rows[0]["Unit"].ToString();
                if (ds.Tables[0].Rows[0]["UnitPrice"].ToString() != "")
                {
                    UnitPrice = decimal.Parse(ds.Tables[0].Rows[0]["UnitPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UnitPriceOut"].ToString() != "")
                {
                    UnitPriceOut = decimal.Parse(ds.Tables[0].Rows[0]["UnitPriceOut"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DepotUp"].ToString() != "")
                {
                    DepotUp = decimal.Parse(ds.Tables[0].Rows[0]["DepotUp"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DepotDown"].ToString() != "")
                {
                    DepotDown = decimal.Parse(ds.Tables[0].Rows[0]["DepotDown"].ToString());
                }
                Color = ds.Tables[0].Rows[0]["Color"].ToString();
                Size = ds.Tables[0].Rows[0]["Size"].ToString();
                Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                if (ds.Tables[0].Rows[0]["Point"].ToString() != "")
                {
                    Point = decimal.Parse(ds.Tables[0].Rows[0]["Point"].ToString());
                }
                ProductProp = ds.Tables[0].Rows[0]["ProductProp"].ToString();
                if (ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                {
                    CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                }
                CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString();

                if (ds.Tables[0].Rows[0]["EndQuantity"].ToString() != "")
                {
                    EndQuantity = decimal.Parse(ds.Tables[0].Rows[0]["EndQuantity"].ToString());
                }

                if (ds.Tables[0].Rows[0]["EndMoney"].ToString() != "")
                {
                    EndMoney = decimal.Parse(ds.Tables[0].Rows[0]["EndMoney"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Avgprice"].ToString() != "")
                {
                    Avgprice = decimal.Parse(ds.Tables[0].Rows[0]["Avgprice"].ToString());
                }
                UnitName = ds.Tables[0].Rows[0]["UnitName"].ToString();
                SupplierName = ds.Tables[0].Rows[0]["SupplierName"].ToString();
                if (ds.Tables[0].Rows[0]["UsefulLifeType"] != null)
                {
                    this.UsefulLifeType = ds.Tables[0].Rows[0]["UsefulLifeType"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BaseProductPlace"] != null)
                {
                    this.BaseProductPlace = ds.Tables[0].Rows[0]["BaseProductPlace"].ToString();
                }
            }
        }


        public DataSet GetProductList(string strWhere, string sDepotID)
        {
            SqlParameter[] parameters = { 
                new SqlParameter("@ac_where", SqlDbType.VarChar, 500),
                new SqlParameter("@ac_DepotID", SqlDbType.VarChar, 10)
                 };
            parameters[0].Value = strWhere;
            parameters[1].Value = sDepotID;
            DataSet ds = DbHelperSQL.RunProcedure("dbo.GetProductList", parameters, "Num");

            return ds;
        }

        public decimal GetEndQuantityByProductID(string sProductID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" Isnull(dbo.f_getProductEndQuantity(a.ProductID),0) EndQuantity");
            strSql.Append(" FROM T_Product a ");
            strSql.Append(" where ProductID = '" + sProductID + "'");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            try
            {
                return decimal.Parse(ds.Tables[0].Rows[0]["EndQuantity"].ToString());
            }
            catch (Exception)
            {
                return 0;
            }
        }


        public DataSet GetListPhoto(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ProductID,ProductNo,ProductName,ProductNameEn,ProductType,SupplierID,Spec1, ");
            strSql.Append("Spec2,Spec3,Unit,UnitPrice,UnitPriceOut,DepotUp,DepotDown,Photo,Color,Size,Remark, ");
            strSql.Append(" Point,ProductProp,CreateDate,CreateUser,");
            strSql.Append(" Isnull(dbo.f_getProductEndQuantity(a.ProductID),0) EndQuantity,");
            strSql.Append(" Isnull(dbo.f_getProductEndMoney(a.ProductID),0) EndMoney,");
            strSql.Append(" Isnull(dbo.f_getProductAvgprice(a.ProductID),0) Avgprice,");
            strSql.Append(" (Select SupplierName from T_Supplier where SupplierID=a.SupplierID) SupplierName,");
            strSql.Append(" (Select CodeName from C_ProductType where CodeID=a.ProductType) ProductTypeName,");
            strSql.Append(" (Select CodeName from C_Unit where CodeID=a.Unit) UnitName");
            strSql.Append(" FROM T_Product a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetProductMeterialList(string strWhere, string NeedNum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Pd.*,");
            strSql.Append("(EndQuantity - NeedQuantity) EfQuantity,");
            strSql.Append("(EndQuantity - NeedQuantity)*UnitPrice EfMoney");
            strSql.Append(" from ");
            strSql.Append("(");
            strSql.Append("select b.ProductID,b.MProductID,b.Quantity," + NeedNum + " NeedNum,");
            strSql.Append("(" + NeedNum + "*b.Quantity) NeedQuantity,");
            strSql.Append("dbo.f_getProductEndQuantity(a.ProductID) EndQuantity,");
            strSql.Append("ProductName MeterialName,UnitPrice,DepotUp,DepotDown,");
            strSql.Append("(Select ProductName from T_Product where ProductID=b.ProductID) ProductName,");
            strSql.Append("(Select SupplierName from T_Supplier where SupplierID=a.SupplierID) SupplierName,");
            strSql.Append("(Select CodeName from C_ProductType where CodeID=a.ProductType) ProductTypeName,");
            strSql.Append("(Select CodeName from C_Unit where CodeID=a.Unit) UnitName");
            strSql.Append(" FROM T_Product a,T_ProductDetail b");
            strSql.Append(" where a.ProductID=b.MProductID");
            strSql.Append(" " + strWhere + " ");
            strSql.Append(") Pd");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetProductType(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  a.CodeID ProductTypeID,CodeName ProductTypeName,Remark ProductProp, ");
            strSql.Append("(Select CodeName from c_ProductProp where CodeID=a.Remark) ProductPropName ");
            strSql.Append(" from c_dmb a where DDType='ProductType'");
            if (strWhere != "")
            {
                strSql.Append(" " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetProductDetail(string sProductID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select b.ProductID,b.ProductName,Quantity PlnQuantity,Quantity ActQuantity,Quantity ShpQuantity,0 BckQuantity, ");
            strSql.Append(" UnitName,UnitPrice,Quantity*UnitPrice SumPrice,b.SupplierName ");
            strSql.Append(" from T_ProductDetail a,S_Product b ");
            strSql.Append(" where a.MProductID=b.ProductID ");
            strSql.Append(" and a.ProductID='" + sProductID + "'");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetRptStock(string OrderDate1, string OrderDate2, string ProductType, string DepotID, string sProductName)
        {
            SqlParameter[] parameters = { 
                new SqlParameter("@OrderDate1", SqlDbType.VarChar, 8),
                new SqlParameter("@OrderDate2", SqlDbType.VarChar, 8),
                new SqlParameter("@ProductType", SqlDbType.VarChar, 50),
                new SqlParameter("@DepotID", SqlDbType.VarChar, 50),
                new SqlParameter("@ProductName", SqlDbType.VarChar, 1000)
                 };
            parameters[0].Value = OrderDate1;
            parameters[1].Value = OrderDate2;
            parameters[2].Value = ProductType;
            parameters[3].Value = DepotID;
            parameters[4].Value = sProductName;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.sp_DepotCount", parameters, "Num");
            return ds;
        }

        public static DataSet GetRptStockDetail(string OrderDate1, string OrderDate2, string ProductType, string DepotID, string ProductID)
        {
            SqlParameter[] parameters = { 
                new SqlParameter("@OrderDate1", SqlDbType.VarChar, 8),
                new SqlParameter("@OrderDate2", SqlDbType.VarChar, 8),
                new SqlParameter("@ProductType", SqlDbType.VarChar, 50),
                new SqlParameter("@DepotID", SqlDbType.VarChar, 50),
                new SqlParameter("@ProductID", SqlDbType.VarChar, 50)
                 };
            parameters[0].Value = OrderDate1;
            parameters[1].Value = OrderDate2;
            parameters[2].Value = ProductType;
            parameters[3].Value = DepotID;
            parameters[4].Value = ProductID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.sp_DepotDetail", parameters, "Num");
            return ds;
        }
    }
}

